#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

${HIVE_HOME} -S -e "
create database if not exists  oe_dws;
drop table if exists  oe_dws.dws_customer_clue_detail;
create table oe_dws.dws_customer_clue_detail(
    line string    comment '线上线下',
    date_day     string    comment  '日维度',
    date_hour     string    comment  ' 小时维度',
    clue string    comment '线索状态',
    total_cnt bigint comment '总线索个数',
    count_clue bigint  comment '有效线索个数',
    rate   decimal(38,2)  comment '有效线索转化率'
)COMMENT '线索主题宽表'
row format delimited fields terminated by '\t'
stored as TextFile
tblproperties ('TextFile.compress' = 'SNAPPY');

drop table if exists   oe_dws.dws_customer_relationship_detail;
create table oe_dws.dws_customer_relationship_detail(
        a_day string  comment   '日维度',
        area  string  comment '区域',
        subject_id  int  comment '学科id',
        subject_name  string  comment '学科名称',
        school_id  int  comment '校区id',
        school_name  string  comment '校区名称',
        origin_channel  string  comment '来源渠道',
        dp_id  int  comment  '部门id',
        name  string  comment '部门名称',
        line   string comment  '线上线下维度',
        CLUE   string comment  ' 新老线索学员',
        group_type  string  comment '分组',
        count_cr  bigint  comment '意向用户个数'
)COMMENT '意向主题宽表'
row format delimited fields terminated by '\t'
stored as TextFile
tblproperties ('TextFile.compress' = 'SNAPPY');

insert into oe_dws.dws_customer_clue_detail
with top as (
    select
       origin_type,
       appeal_status,
       id,
       clue_state,
       is_repeat,
       appeal_id,
       substring(cast(create_date_time as varchar(30)),1,10) as date_day,
       substring(cast(create_date_time as varchar(30)),1,13) as date_hour,
       case when origin_type = 'NETSERVICE' or origin_type = 'PRESIGNUP'
        then 'online'
        else 'offline' end as line,
        case when clue_state = 'VALID_PUBLIC_NEW_CLUE' OR clue_state = 'VALID_NEW_CLUE'
        then 'NEW'
        else 'OLD' end as clue
from oe_dwb.dwb_customer_relationship_detail

)
select


          line,
         date_day ,
         date_hour,
         clue,
        count(id) as  total_cnt,-- 总线索个数
        count(id) - count (if(appeal_status = 1 ,id,null)) as count_clue ,--有效线索个数
--         count (if(appeal_status = 0 or appeal_status = 2,id,null)) as count_clue, -- 有效线索个数
        cast (count(id) - count (if(appeal_status = 1 ,id,null)) as decimal(38,2)) / cast(count(id) as decimal(38,2))  as rate
--         cast (count(if(appeal_status=0 or appeal_status = 2,id,null)) as decimal(38,2)) / cast(count(id) as decimal(38,2))  as rate

from top
group by
grouping sets(
    (date_day,line,clue),
    (date_hour,line,clue)
);

insert into  oe_dws.dws_customer_relationship_detail
 with temp as (select
-- 维度抽取
       school_id,
       school_name,
       subject_id,
       subject_name,
       area,
       origin_type,
       origin_channel,
       clue_state,
       dp_id,
       name,
-- 用户意向指标
       customer_relationship_id,
-- 判断条件
       appeal_status,
       is_repeat,
       create_date_time,
       substring(cast(create_date_time as varchar(30)),1,10) as a_day,
       substring(cast(create_date_time as varchar(30)),1,7) as a_month,
       substring(cast(create_date_time as varchar(30)),1,4) as a_year,
       case when origin_type = 'NETSERVICE' or origin_type = 'PRESIGNUP'
        then 'online'
        else 'offline' end as line,
        case when clue_state = 'VALID_PUBLIC_NEW_CLUE' OR clue_state = 'VALID_NEW_CLUE'
        then 'NEW'
        else 'OLD' end as CLUE,
    row_number() over (partition by customer_relationship_id ) as cr_rn
from oe_dwb.dwb_customer_relationship_detail )

 select a_day,
        area,
        subject_id,
        subject_name,
        school_id,
        school_name,
        origin_channel,
        dp_id,
        name,
        line,
        CLUE,
        CASE WHEN GROUPING(area) = 0 THEN 'area'
            WHEN GROUPING(subject_id) = 0 AND GROUPING(subject_name) = 0 THEN 'subject'
            WHEN GROUPING(school_id) = 0 AND GROUPING(school_name) = 0 THEN 'school'
            WHEN GROUPING(origin_channel) = 0 THEN 'channel'
            WHEN GROUPING(dp_id) = 0 AND GROUPING(name) = 0 THEN 'dp'
            END AS group_type,
        count ( customer_relationship_id ) as count_cr
        from temp
where cr_rn = 1
group by
grouping sets (
(a_day,line,CLUE),
(a_day,line,CLUE,area),
(a_day,line,CLUE,subject_id,subject_name),
(a_day,line,CLUE,school_id,school_name),
(a_day,line,CLUE,origin_channel),
(a_day,line,CLUE,dp_id,name)
);




"